/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.pb.diplom.first.dao;

import com.pb.diplom.first.tables.Commodity;
import com.pb.diplom.first.tables.CommodityJoin;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Slava
 */
public class CommodityJoinDao {

    private final String login;
    private final String password;
    private final String url;

    public CommodityJoinDao(String login, String password, String url) {
        this.login = login;
        this.password = password;
        this.url = url;
    }

    public List<CommodityJoin> getAll() {
        List<CommodityJoin> goods = new ArrayList<>();
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            con = DriverManager.getConnection(url, login, password);
            stmt = con.prepareStatement("SELECT t1.model, t2.name_manufacturer, t3.name_category, t1.description, t1.price, t1.storage FROM Good as t1 JOIN Manufacturer as t2 on t1.ID_manufacturer=t2.ID_manufacturer JOIN Category as t3 on t1.ID_category=t3.ID_category");
            rs = stmt.executeQuery();
            while (rs.next()) {
                CommodityJoin commodity = new CommodityJoin();
                commodity.setModel(rs.getString(1));
                commodity.setNameManufacturer(rs.getString(2));
                commodity.setNameCategory(rs.getString(3));
                commodity.setDescription(rs.getString(4));
                commodity.setPrice(rs.getBigDecimal(5));
                commodity.setStorage(rs.getString(6));
                goods.add(commodity);
            }
        } catch (SQLException ex) {
            Logger.getLogger(CommodityJoinDao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Logger.getLogger(CommodityJoinDao.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    Logger.getLogger(CommodityJoinDao.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    Logger.getLogger(CommodityJoinDao.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return goods;
    }

    public void save(CommodityJoin commodity) throws SQLException {
        Connection con = null;
        Statement stmt = null;
        try {
            con = DriverManager.getConnection(url, login, password);
            stmt = con.createStatement();
            //stmt.executeUpdate("INSERT INTO Category values(" + commodity.getId() + ", '" + commodity.getModel() + "', " + commodity.getCategoryId() + ", " + commodity.getPrice() + ", '" + commodity.getDescription() + "', '" + commodity.getImage() + "', '" + commodity.getStorage() + "' )");
        } finally {
            if (stmt != null) {
                stmt.close();
            }
            if (con != null) {
                con.close();
            }
        }
    }
//
//    public Long getId(Commodity com) throws SQLException {
//        Long l = null;
//        Connection con = null;
//        PreparedStatement stmt = null;
//        ResultSet rs = null;
//        try {
//            //Long l;
//            con = DriverManager.getConnection(url, login, password);
//            stmt = con.prepareStatement("SELECT ID_category FROM Category WHERE name_category= '" + com.getModel() + "'");
//            rs = stmt.executeQuery();
//            while (rs.next()) {
//                Commodity category = new Commodity();
//                l = rs.getLong(1);
//            }
//        } finally {
//            if (rs != null) {
//                rs.close();
//            }
//            if (stmt != null) {
//                stmt.close();
//            }
//            if (con != null) {
//                con.close();
//            }
//        }
//        return l;
//    }

}
